


********************************************************************************
*Part IV: Quarterly Census of Employment and Wages

{
cd "~/DataFolder"

use QCEW.dta, clear // Downloaded from U.S. Bureau of Labor Statistics

*keep only obs at 3-digit NAICS level
drop if real(industry_code)==.
destring industry_code, replace
gen temp1=int(industry_code/100)
gen temp2=int(industry_code/1000)
order area_fips own_code temp1 temp2 *
keep if temp1 !=0 & temp2 ==0
drop temp1 temp2

*keep
keep if agglvl_code==75
keep if own_code==5

rename area_fips fips
drop if missing(fips)
destring fips, replace

rename industry_code naics3
gen naics2=int(naics3/10)
drop if naics2==52
drop naics2
rename annual_avg_emplvl emplvl
keep fips year own_code naics3 emplvl
duplicates drop fips year naics3, force


gsort fips year -emplvl
bysort fips year: egen rank=rank(-emplvl) 
bysort fips year: egen templvl=total(emplvl)
gen selfpct=emplvl/templvl
save CountyJob_All.dta, replace 

*
}
*

********************************************************************************
*****************Part IV: Merge Bank Specialization with County-Level Occupation
{

*** Main Specification
cd "~/DataFolder"
use KeyMeasure, clear

joinby year naics3 using CountyJob_All
drop if missing(selfpct)
keep if selfpct>=0.05

sort rssdid year fips naics 
order rssdid year fips naics
bysort rssdid year fips: egen numspec_above5pctemp=total(spec_above5pctemp)
bysort rssdid year fips: egen aggemppct_above5pctemp=total(selfpct)
bysort rssdid year fips: egen bankabove_above5pctemp=total(bankspec_above)

duplicates drop rssdid year fips, force

keep rssdid year fips  spec numspec f3pct aggf3pct bankabove 

rename rssdid rssdhcr
save KeyMeasure2, replace


*** Robustness
* Weighting all industries in each county
cd "~/DataFolder"
use KeyMeasure, clear

joinby year naics using CountyJob_All

bysort rssdid year fips: egen aggAllpct=total(selfpct)
duplicates drop rssdid year fips, force

keep rssdid year fips aggAllpct
rename rssdid rssdhcr
save KeyMeasure2_AllInd, replace

* Size-Adjusted weight as bank specialization 
cd "~/DataFolder"
use KeyMeasure_wt

joinby year naics using CountyJob

duplicates drop rssdid year fips, force

keep rssdid year fips spec numspec
rename rssdid rssdhcr
save KeyMeasure2_wt, replace

}


